package com.epin.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * @ClassName ExportExcelUtil
 * @Description excel通用导出
 * @author Zaku Xu
 *
 */
public class ExportExcelUtils {
	
	/**
	 * <p>Title: 构造函数</p> 
	 * <p>Description: poi excel通用导出</p>
	 */
	private ExportExcelUtils() {

	}

	/**
	 * 
	 */
	private static HSSFWorkbook workbook;

	/***
	 * sheet
	 */
	private static HSSFSheet sheet;

	/**
	 * @Title excelExport 
	 * @Description 导出excel
	 * @param dataList 数据集合
	 * @param titleMap 表头信息（对象属性名称->要显示的标题值)[按顺序添加]
	 * @param sheetName sheet名称
	 * @return 文件保存的绝对路径
	 */
	public static String excelExport(List<?> dataList, LinkedHashMap<String, String> titleMap, String sheetName, String exportFileName) {
		// 初始化workbook
		initHSSFWorkbook(sheetName);
		
		createHeaderRow(0, titleMap);
		
		createContentRow(1, dataList, titleMap);
		
		// 设置自动伸缩
		autoSizeColumn(titleMap.size());
		// 写入处理结果
		String filedisplay = "";
		try {
			String fileName = exportFileName;
			// 生成UUID文件名称
			if(StringUtils.isBlank(exportFileName)){
				UUID uuid = UUID.randomUUID();
				fileName = uuid + ".xls";
			}
			// 如果web项目，1、设置下载框的弹出（设置response相关参数)；2、通过httpservletresponse.getOutputStream()获取
			String downPath = FilePropertyUtil.getFilePathProperty().getProperty("downPath");
			filedisplay = downPath + "\\" + fileName;
			OutputStream out = new FileOutputStream(filedisplay);
			workbook.write(out);
			out.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return filedisplay;
	}

	/**
	 * @Title initHSSFWorkbook 
	 * @Description 初始化工作薄
	 * @param sheetName
	 */
	private static void initHSSFWorkbook(String sheetName) {
		workbook = new HSSFWorkbook();
		sheet = workbook.createSheet(sheetName);
	}

	/**
	 * @Title createHeadRow 
	 * @Description 创建表头行
	 * @param titleMap 标题行
	 */
	private static void createHeaderRow(Integer position, LinkedHashMap<String, String> titleMap) {
		// 第1行创建
		HSSFRow headRow = sheet.createRow(position);
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		int i = 0;
		for (String key : titleMap.keySet()) {
			HSSFCell headCell = headRow.createCell(i);
			headCell.setCellStyle(cellStyle);
			headCell.setCellValue(titleMap.get(key));
			i++;
		}
	}

	/**
	 * @Title createContentRow 
	 * @Description 创建行
	 * @param dataList
	 * @param titleMap
	 */
	private static void createContentRow(Integer position, List<?> dataList, LinkedHashMap<String, String> titleMap) {
		try {
			int i = 0;
			for (Object obj : dataList) {
				HSSFRow textRow = sheet.createRow(position + i);
				int j = 0;
				for (String key : titleMap.keySet()) {
					String method = "get" + key.substring(0, 1).toUpperCase() + key.substring(1);
					Method m = obj.getClass().getMethod(method, null);
					String value ="";
					if (null != m.invoke(obj, null)) {
						value = m.invoke(obj, null).toString();
					} 
					HSSFCell textcell = textRow.createCell(j);
					textcell.setCellValue(value);
					j++;
				}
				i++;
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * @Title autoSizeColumn 
	 * @Description 自动伸缩列（如非必要，请勿打开此方法，耗内存）
	 * @param size 列数
	 */
	private static void autoSizeColumn(Integer colsSize) {
		for (int j = 0; j < colsSize; j++) {
			sheet.autoSizeColumn(j);
		}
	}
	
	
	/**
	 * 根据给定的 标题+数据 导出Excel
	 * @Title exportExcelFromDataMap 
	 * @Description 这里用一句话描述这个方法的作用
	 * @param title
	 * @param data
	 * @param savePath eg:request.getSession().getServletContext().getRealPath("")+File.separator+"file"+File.separator+xxx.xls
	 */
	public static void exportExcelFromDataMap(List<String> keys, List<String> titles,  List<Map> data, String savePath){
		WritableWorkbook workbook = null;
		WritableSheet sheet = null;
		try {
			if(keys!=null && keys.size()>0 && titles!=null && titles.size()>0 && keys.size()==titles.size() && data!=null && data.size()>0 && StringUtils.isNotBlank(savePath)){
				//0.设置Excel文件输出路径
				File file = new File(savePath);
				//如果当前文件路径未创建,则先创建对应保存目录
				if(!file.getParentFile().exists()){
					file.mkdirs();
				}
				//如果当前文件已存在,则认为是上次保留的Excel文件,删除
				if(file.exists()){
					file.delete();
				}
				//1.创建工作簿
				workbook = jxl.Workbook.createWorkbook(file);
				//2.创建工作表Sheet
				sheet = workbook.createSheet("Sheet1", 0);
				
				//设置纵横打印（默认为纵打）、打印纸
				jxl.SheetSettings sheetset = sheet.getSettings();  
				sheetset.setProtected(false); 
				
				//设置三种单元格样式
				WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);  
				WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
				//单元格样式:标题居中
				WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);  
				wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
				wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
				wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐  
				wcf_center.setWrap(false); // 文字是否换行 
				//单元格样式:正文居左
				WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);  
				wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
				wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐  
				wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐  
				wcf_left.setWrap(false); // 文字是否换行 
				
				jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00"); // 设置数字格式  
	            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式 
	            wcfN.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
	            
	            jxl.write.NumberFormat nf1 = new jxl.write.NumberFormat("#0.0000"); // 设置数字格式  
	            jxl.write.WritableCellFormat wcfN1 = new jxl.write.WritableCellFormat(nf1); // 设置表单格式 
	            wcfN1.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条  
	            
				//3.获取 标题栏内容
				int size = titles.size();
				for(int i=0; i<size; i++){
					String tt = titles.get(i);
					sheet.addCell(new Label(i, 0, tt,wcf_center));  
				}
				
				//4.填充 正文内容
				int length = data.size();
				for(int j=0; j<length; j++){
					Map<String, Object> map = data.get(j);
					if(map!=null && !map.isEmpty()){
						for(int k=0; k<size; k++){
							String key=keys.get(k);
							
							if(StringUtils.isNotBlank(key)){
								String content = "";
								if(map.containsKey(key)){
									Object o = map.get(key);
									if(o!=null){
										content = o+"";
									}
									
									//费用转为数字
									if (StringUtils.isNotBlank(key) && (key.indexOf("expenses_code#") != -1 || "bill#GROSS_WEIGHT".equals(key.trim()) || "bill#NET_WEIGHT".equals(key.trim())) && StringUtils.isNotBlank(content)){
										try {  
				                            double titlesDoubleValue = Double.parseDouble(content);  
				                            
				                            if ("bill#GROSS_WEIGHT".equals(key.trim()) || "bill#NET_WEIGHT".equals(key.trim())){
				                            	jxl.write.Number labelNF = new jxl.write.Number(k, j+1, titlesDoubleValue, wcfN1); // 格式化数值  
				                            	sheet.addCell(labelNF);  
				                            } else {
				                            	jxl.write.Number labelNF = new jxl.write.Number(k, j+1, titlesDoubleValue, wcfN); // 格式化数值  
				                            	sheet.addCell(labelNF);  
				                            }
				                        } catch (Exception e) {  
				                        	e.printStackTrace();
				                            // 这里需要注意的是，在Excel中，j表示列，row表示行  
				                        	sheet.addCell(new Label(k, j+1, content, wcf_left)); 
				                        }  
									} else {
										sheet.addCell(new Label(k, j+1, content, wcf_left)); 
									}
								} else {
									sheet.addCell(new Label(k, j+1, content, wcf_left)); 
								}
							}
						}
						
					}
				}
				//写入数据
				workbook.write();
				//System.out.println("Excel生成完毕!");
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		} finally{
			if(workbook!=null){
				try {
					workbook.close();
					workbook = null;
				} catch (WriteException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
}
